http://t.zoukankan.com/GuoSamael-p-9766113.html
SqlServer字段说明查询及快速查看表结构

1.查询数据库中的所有数据库名：

SELECT Name FROM Master..SysDatabases ORDER BY Name
2.查询某个数据库中所有的表名：

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
3.SqlServer字段说明查询
SELECT t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value] 
  as varchar(100)) AS [字段说明]
  FROM sys.tables AS t
  INNER JOIN sys.columns 
  AS c ON t.object_id = c.object_id
   LEFT JOIN sys.extended_properties AS ep 
  ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1 
  AND t.name='TableName'


4.查看表名注释

SELECT DISTINCT
    d.name,
    f.value 
FROM
    syscolumns a
    LEFT JOIN systypes b ON a.xusertype= b.xusertype
    INNER JOIN sysobjects d ON a.id= d.id 
    AND d.xtype= 'U' 
    AND d.name<> 'dtproperties'
    LEFT JOIN syscomments e ON a.cdefault= e.id
    LEFT JOIN sys.extended_properties g ON a.id= G.major_id 
    AND a.colid= g.minor_id
    LEFT JOIN sys.extended_properties f ON d.id= f.major_id 
    AND f.minor_id= 0
WHERE d.name='table_name'


5.快速查看表结构
--快速查看表结构（比较全面的）
SELECT  obj.name AS 表名,
        col.colorder AS 序号 ,
        col.name AS 列名 ,
        ISNULL(ep.[value], '') AS 列说明 ,
        t.name AS 数据类型 ,
        col.length AS 长度 ,
        ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
        CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
             ELSE ''
        END AS 标识 ,
        CASE WHEN EXISTS ( SELECT   1
                           FROM     dbo.sysindexes si
                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                              AND si.indid = sik.indid
                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                              AND sc.colid = sik.colid
                                    INNER JOIN dbo.sysobjects so ON so.name = si.name
                                                              AND so.xtype = 'PK'
                           WHERE    sc.id = col.id
                                    AND sc.colid = col.colid ) THEN '√'
             ELSE ''
        END AS 主键 ,
        CASE WHEN col.isnullable = 1 THEN '√'
             ELSE ''
        END AS 允许空 ,
        ISNULL(comm.text, '') AS 默认值
FROM    dbo.syscolumns col
        LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
        inner JOIN dbo.sysobjects obj ON col.id = obj.id
                                         AND obj.xtype = 'U'
                                         AND obj.status >= 0
        LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
        LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                      AND col.colid = ep.minor_id
                                                      AND ep.name = 'MS_Description'
        LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                                                         AND epTwo.minor_id = 0
                                                         AND epTwo.name = 'MS_Description'
WHERE   obj.name = 'TableName'--表名
ORDER BY obj.name,col.colorder ;
 


---SQLSERVER查询所有约束 https://www.cnblogs.com/yangdunqin/articles/ys.html
SELECT   TAB.NAME AS [表名], '主键' AS [是否唯一索引], IDXCOL.IS_DESCENDING_KEY AS [是否降序], IDX.NAME AS [约束名称],
                IDX.TYPE_DESC AS [约束类型], COL.NAME AS [约束列名], INDEX_COLUMN_ID
FROM      SYS.INDEXES IDX JOIN
                SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND
                IDX.IS_PRIMARY_KEY = 1) JOIN
                SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
                SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID)
UNION ALL
SELECT   TAB.NAME AS [表名], 'UQ唯一索引' AS [是否唯一索引], IDXCOL.IS_DESCENDING_KEY AS [是否降序],
                IDX.NAME AS [约束名称], IDX.TYPE_DESC AS [约束类型], COL.NAME AS [约束列名], INDEX_COLUMN_ID
FROM      SYS.INDEXES IDX JOIN
                SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND
                IDX.IS_UNIQUE_CONSTRAINT = 1) JOIN
                SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
                SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID)
UNION ALL
SELECT   TAB.NAME AS [表名], CASE IDX.IS_UNIQUE WHEN 0 THEN '非唯一索引' ELSE '唯一索引' END AS [是否唯一索引],
                IDXCOL.IS_DESCENDING_KEY AS [是否降序], IDX.NAME AS [约束名称], IDX.TYPE_DESC AS [约束类型],
                COL.NAME AS [约束列名], INDEX_COLUMN_ID
FROM      SYS.INDEXES IDX JOIN
                SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND
                IDX.IS_UNIQUE_CONSTRAINT = 0 AND IS_PRIMARY_KEY = 0) JOIN
                SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
                SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID)


--查看表约束信息
SELECT  *
FROM    sysobjects
WHERE   OBJECT_NAME(parent_obj) = 'DHProductOrder'






https://zhidao.baidu.com/question/1993301066173145027.html
--主键约束
SELECT
tab.name AS [表名],
idx.name AS [主键名称],
col.name AS [主键列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_primary_key = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
--唯一约束
SELECT
tab.name AS [表名],
idx.name AS [约束名称],
col.name AS [约束列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_unique_constraint = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
--外键约束
select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
--Check约束
SELECT
tab.name AS [表名],
chk.name AS [Check约束名],
col.name AS [列名],
chk.definition
FROM
sys.check_constraints chk
JOIN sys.tables tab
ON (chk.parent_object_id = tab.object_id)
JOIN sys.columns col
ON (chk.parent_object_id = col.object_id
AND chk.parent_column_id = col.column_id)
































exec sp_helpconstraint [dbo].[student] ; --用系统存储过程查看student表的约束；
select * from sys.key_constraints ; --查看数据库中的主键约束；
select * from sys.objects where type in('C','D','F','PK','UQ') ; --查看数据库的所有约束；
select * from sys.objects where type in('C','D','F','PK','UQ') ;-- 'C'，CHECK约束 'D'，默认值或DEFAULT约束 'F'，FOREIGN KEY约束 'PK'，PRIMARY KEY约束 'UQ'，UNIQUE约束。


































